﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text.RegularExpressions;
using System.Threading;
using Jeelu.Data.Common;
using NLog;

namespace Jeelu.Data.SQLite
{
    public class Database : DatabaseHelper
    {
        #region const

        /// <summary>连接字符串
        /// </summary>
        public const string CONN_STRING = "Data Source={0}; Pooling=true; FailIfMissing=false";
        public const string ALL_TABLE = "SELECT tbl_name FROM sqlite_masters";
        public const string HAS_TABLE = "SELECT count(*) FROM sqlite_master WHERE name='{0}'";

        /// <summary>建表语句，含3个占位符。0.表名；1.主键字段名；2.其他字段名
        /// </summary>
        public const string CREAT_TABLE = "Create TABLE [{0}]({1},{2})";

        public const string SELECT_CREAT_TABLE = "SELECT sql FROM sqlite_master WHERE tbl_name='{0}' AND type='table'";
        public const string INSERT = "INSERT INTO {0} VALUES ({1})";
        public const string UPDATE = "UPDATE {0} SET {1} WHERE {2}";

        #endregion

        private static readonly Logger _Logger = LogManager.GetCurrentClassLogger();

        private static string _DbConnectionString;
        private static string _DbFile;
        private IDbConnection _Connection;

        internal static string[] GetFields(string table, Func<string, string> func)
        {
            string selectCreatTableSql = String.Format(SELECT_CREAT_TABLE, table);
            string sql = func.Invoke(selectCreatTableSql);
            var rx = new Regex(@"\[.*\]", RegexOptions.Multiline); //多行模式
            MatchCollection groups = rx.Matches(sql);
            var fields = new List<string>();
            for (int i = 1; i < groups.Count; i++)
            {
                string value = groups[i].Value;
                fields.Add(value.Substring(1, value.Length - 2));
            }
            return fields.ToArray();
        }

        #region Overrides of DatabaseHelper

        public override string ConnectionString
        {
            get
            {
                if (!string.IsNullOrWhiteSpace(_DbFile))
                {
                    _DbConnectionString = String.Format("Data Source={0}; Pooling=true; FailIfMissing=false", _DbFile);
                }
                return _DbConnectionString;
            }
        }

        public override IDbConnection Connection
        {
            get { return _Connection ?? (_Connection = new SQLiteConnection(ConnectionString)); }
        }

        public override IDbCommand Command
        {
            get { return new SQLiteCommand((SQLiteConnection) Connection); }
        }

        public override bool NeedConnectionClose
        {
            get { return false; }
        }

        public override bool Initialize(params object[] args)
        {
            try
            {
                //Pooling设置为true时，SQL连接将从连接池获得，如果没有则新建并添加到连接池中,默认是true。
                //FailIfMissing默认为false，如果数据库文件不存在，会自动创建一个新的，若设置为true，将不会创建，而是抛出异常信息。
                _DbFile = args[0].ToString();
                _DbConnectionString = String.Format(CONN_STRING, _DbFile);
                _Logger.Info(String.Format("数据库文件:{0}", _DbFile));
                Check(new SQLiteConnection(_DbConnectionString));
                return true;
            }
            catch (Exception e)
            {
                _Logger.WarnException("初始化数据库异常", e);
                return false;
            }
        }

        public override IDbDataAdapter GetAdapter(IDbCommand command)
        {
            return new SQLiteDataAdapter((SQLiteCommand) command);
        }

        #endregion

        #region Database 文件处理

        private static IDbConnection _DbConnection;
        private static ushort _CheckCount = 1;

        private static string FileName { get; set; }

        private static void Check(IDbConnection connection)
        {
            _DbConnection = connection;
            try
            {
                using (_DbConnection)
                {
                    _DbConnection.Open();
                    using (var command = new SQLiteCommand((SQLiteConnection) _DbConnection))
                    {
                        command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
                        command.ExecuteNonQuery();

                        command.CommandText = "DROP TABLE Demo";
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                _Logger.WarnException("数据库访问异常。" + _CheckCount, e);
                _DbConnection.Close();
                ProcessFile();
            }
        }

        private static void ProcessFile()
        {
            try
            {
                File.Copy(FileName, String.Format("{0}.{1}.error", FileName, _CheckCount), true);
                File.Delete(FileName);
            }
            catch (Exception e)
            {
                _Logger.ErrorException("文件访问错误", e);
                FileName = FileName.Insert(FileName.LastIndexOf('.'), "." + _CheckCount);
            }
            if (_CheckCount < 8)
            {
                Thread.Sleep(1000 + 400*_CheckCount);
                _CheckCount++;
                _DbConnection.ConnectionString = String.Format(CONN_STRING, FileName);
                Check(_DbConnection);
            }
            else
            {
                _Logger.Error("本地数据存储访问失效。尝试8次无法处理");
            }
        }

        #endregion

        #region 单件实例

        private static readonly Lazy<Database> _Instance = new Lazy<Database>(() => new Database());

        /// <summary> 获得一个本类型的单件实例.
        /// </summary>
        /// <value>The instance.</value>
        public static Database Instance
        {
            get { return _Instance.Value; }
        }

        #endregion

    }
}